<?php
/*
 * ebiz front
 *
 * ProductDao Model
 *
 * @author allen
 *
 */
require_once '../app/core/models/BaseDao.php';
require_once '../app/core/db/DBUtil.class.php';

class ProductDaoTest extends BaseDao
{

    private $tableName = "products";

    public function __construct($dbUtil){
        parent::__construct($dbUtil);
        $this->_name = $this->tableName;
    }

    public function getByProductId($id){
        $param = array($id);
        $sql = "select * from $this->tableName where id = ? and is_delete = 0";
        return $this->dbUtil->SelectData($sql, $param);
    }

    public function getByUrlTitle($urlTitle){
        $param = array($urlTitle);
        $sql = "select * from $this->tableName where url_title = ? and is_delete = 0";
        return $this->dbUtil->SelectData($sql, $param);
    }

    public function getByPids($ids, $currentPage, $pagesize){     //give the string pid of separator by ','  to get the paginate product info , base on pid
    	$offset=($currentPage - 1) * $pagesize;
    	$ids = mysql_escape_string($ids);
        $param = array();
//        $sql = "select * from $this->tableName join product_items on " . $this->tableName . ".id = product_items.product_id where products.id in ($ids) and products.is_delete = 0 and product_items.is_delete = 0 group by product_items.product_id limit $offset, $pagesize ";
//        $sql = "select pi.id as pi_id,pi.list_price,pi.sale_price,p.id as p_id,p.title,p.url_title,pim.folder,pim.name from  product_items pi join $this->tableName p on p.id = pi.product_id  left join product_images pim on pim.item_id = pi.id and pim.default_item = 1 and pim.default_item_image = 1 and pim.is_delete = 0 where p.id in ($ids) and pi.is_delete = 0 and p.is_delete = 0 group by pi.product_id limit $offset, $pagesize ";
//        $sql = "select pi.id as pi_id,pi.list_price,pi.sale_price,p.id as p_id,p.title,p.url_title,pim.folder,pim.name,pim.default_item_image,pim.default_item  from  product_items pi join $this->tableName p on p.id = pi.product_id left join product_images pim on pim.item_id = pi.id  and pim.is_delete = 0  and pim.default_item = 1 and pim.default_item_image = 1  where p.id in ( $ids ) and pi.is_delete = 0 and p.is_delete = 0 group by p.id limit $offset, $pagesize ";
        //0520 modify , because the DB have change , default_item have add to product_items table
        //this sql is right
//        $sql = "select pi.id as pi_id,pi.list_price,pi.sale_price,p.id as p_id,p.title,p.url_title,pim.folder,pim.name,pim.default_item_image,pi.default_item  from  product_items pi join $this->tableName p on p.id = pi.product_id  and pi.default_item = 1  left join product_images pim on pim.item_id = pi.id  and pim.is_delete = 0 and pim.default_item_image = 1  where p.id in ( $ids ) and pi.is_delete = 0 and p.is_delete = 0 group by p.id limit $offset, $pagesize ";
        //this sql is testing sql less a condition is : and pi.default_item = 1
//        $sql = "select pi.id as pi_id,pi.list_price,pi.sale_price,p.id as p_id,p.title,p.url_title,pim.folder,pim.name,pim.default_item_image,pi.default_item  from  product_items pi join $this->tableName p on p.id = pi.product_id    left join product_images pim on pim.item_id = pi.id  and pim.is_delete = 0 and pim.default_item_image = 1  where p.id in ( $ids ) and pi.is_delete = 0 and p.is_delete = 0 group by p.id limit $offset, $pagesize ";
        //0715 add , visible=1 then show in category page
        $sql = "select pi.id as pi_id,pi.list_price,pi.sale_price,pi.shipping_type,pi.free_shipping_status,pi.inventory,pi.weight,
		        p.id as p_id,p.title,p.url_title,p.description,
		        pim.folder,pim.name,pim.default_item_image,pi.default_item
		        from  product_items pi join $this->tableName p on p.id = pi.product_id  and pi.visible = 1
		        left join product_images pim on pim.item_id = pi.id  and pim.is_delete = 0  and pim.default_item_image = 1
		        where p.id in ( $ids ) and  pi.is_delete = 0 and p.is_delete = 0
		        limit $offset, $pagesize ";
        return $this->dbUtil->SelectData($sql, $param);
    }

    public function getAllByPids($ids){     //give the string pid of separator by ','  to get the total count
    	$ids = mysql_escape_string($ids);
    	$param = array();
//        $sql = "select * from $this->tableName join product_items on " . $this->tableName . ".id = product_items.product_id where ". $this->tableName . ".id in ($ids) and products.is_delete = 0 and product_items.is_delete = 0 group by product_items.product_id ";
        $sql = "select * from $this->tableName join product_items on " . $this->tableName . ".id = product_items.product_id where ". $this->tableName . ".id in ($ids) and products.is_delete = 0 and product_items.is_delete = 0 and visible=1 ";
        return $this->dbUtil->SelectData($sql, $param);
    }

    public function getRelateProductByPid($pid){    //get the id to find the relate product info
    	$param = array($pid);
    	$sql = "select pi.id as pi_id,pi.list_price,pi.sale_price,p.id as p_id,p.title,p.url_title,pim.folder,pim.name  from  ". $this->tableName . " p join   product_items pi    on p.id = pi.product_id  and pi.default_item=1  left join product_images pim on pim.item_id = pi.id  and pim.is_delete = 0 and pim.default_item_image=1 where p.id = $pid and pi.is_delete = 0 and p.is_delete = 0 ";
//    	$sql = "select pi.id as pi_id,pi.list_price,pi.sale_price,p.id as p_id,p.title,p.url_title,pim.folder,pim.name  from  product_items pi join ". $this->tableName . " p on p.id = pi.product_id   left join product_images pim on pim.item_id = pi.id  and pim.is_delete = 0  where p.id = $pid and pi.is_delete = 0 and p.is_delete = 0 group by pi.product_id ";
    	return $this->dbUtil->SelectData($sql, $param);
    }
    
    /* Get the product info by product id
     * including the product_item id, the price, the pruduct id, 
     * the title, the url_title, the image folder and 
     * the image name
     * 
     * Added by xiaoyuemian, 2009-11-15 11:55
     * */
    public function getRelateProductByPidCertainPage($pids, $currentPage, $pagesize, $filter){    //get the id to find the relate product info
    	if(empty($filter))
    	    $filter = '';

    	$offset=($currentPage - 1) * $pagesize;
    	$ids = mysql_escape_string($pids);
    	
    	$param = array();
    	$sql = "select pi.id as product_item_id,
    	            pi.marketing_price,pi.list_price, 
    	            pi.sale_price,p.id as product_id,
    	            p.title,p.url_title, 
    	            pim.folder as image_folder,
    	            pim.name as image_name 
    	        from  ". $this->tableName 
    	        . " p join   product_items pi on 
    	        p.id = pi.product_id  and pi.default_item=1  
    	        left join product_images pim on 
    	            pim.item_id = pi.id  and pim.is_delete = 0 and 
    	            pim.default_item_image=1 
    	        where p.id in ( $ids ) and pi.is_delete = 0 
    	            and p.is_delete = 0 
    	            and pi.default_item > 0 
    	            and pi.cost_price > 0 
    	            and pi.inventory > 0 
    	            " .$filter 
    	        ."limit $offset, $pagesize ";
//    	$sql = "select pi.id as pi_id,pi.list_price,pi.sale_price,p.id as p_id,p.title,p.url_title,pim.folder,pim.name  from  product_items pi join ". $this->tableName . " p on p.id = pi.product_id   left join product_images pim on pim.item_id = pi.id  and pim.is_delete = 0  where p.id = $pid and pi.is_delete = 0 and p.is_delete = 0 group by pi.product_id ";
    	return $this->dbUtil->SelectData($sql, $param);
    }
    
    /* Get the count of the products by product id
     * 
     * Added by xiaoyuemian, 2009-11-16 22:07
     * */
    public function getRelateProductCountByPid($pids, $filter){    //get the id to find the relate product info
    	if(empty($filter))
    	    $filter = '';

    	$ids = mysql_escape_string($pids);
    	
    	$param = array();
    	$sql = "select COUNT( DISTINCT `p`.`id` ) AS `rows`
    	        from  ". $this->tableName 
    	        . " p join   product_items pi    on 
    	        p.id = pi.product_id  and pi.default_item=1  
    	        left join product_images pim on 
    	        pim.item_id = pi.id  and pim.is_delete = 0 and 
    	        pim.default_item_image=1 
    	        where p.id in ( $ids ) and pi.is_delete = 0 
    	            and p.is_delete = 0 
    	            and pi.default_item > 0 
    	            and pi.cost_price > 0 
    	            and pi.inventory > 0 " .$filter;
//    	$sql = "select pi.id as pi_id,pi.list_price,pi.sale_price,p.id as p_id,p.title,p.url_title,pim.folder,pim.name  from  product_items pi join ". $this->tableName . " p on p.id = pi.product_id   left join product_images pim on pim.item_id = pi.id  and pim.is_delete = 0  where p.id = $pid and pi.is_delete = 0 and p.is_delete = 0 group by pi.product_id ";
        $result = $this->dbUtil->SelectData($sql, $param);

    	return $result[0]['rows'];
    }

    public function getRandomProduct(){
        $str_sql = "
            SELECT `url_title` FROM `{$this->tableName}` WHERE `is_delete` = '0' ORDER BY RAND() LIMIT 0,1
        ";
        return $this->dbUtil->SelectData($str_sql,array());
    }

    /**
     * 兑换礼品，如果礼品是网站中的商品，根据pid显示出产品的信息
     */
    public function getGiftProductByPid($pid){
        $param = array($pid);
        $sql = "select pi.id as pi_id,pi.list_price,pi.sale_price,
        p.id as p_id,p.title,p.url_title,p.description,
        pim.folder,pim.name  
        from  ". $this->tableName . " p 
        join   product_items pi    on p.id = pi.product_id  and pi.default_item=1  
        left join product_images pim on pim.item_id = pi.id  and pim.is_delete = 0 and pim.default_item_image=1 
        where p.id = $pid and pi.is_delete = 0 and p.is_delete = 0 ";
        return $this->dbUtil->SelectData($sql, $param);
    }
    
}
